﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Threading;
using System.Diagnostics;
using System.Data;
using System.Collections;
using DatabaseEntegreItri.Utils;
using DatabaseEntegreItri.DataModel;

namespace DatabaseEntegreItri
{
    // delegate definition
    public delegate void UpdateCompletedHandler(String result);
    public delegate void UpdatePausedHandler();
    public delegate void UpdateProgressHandler(int currentData, int maxDataCount,double remainingMilis);
    public delegate void UpdateStatusHandler(String result);

    public class LocumUpdate
    {
        public event UpdateCompletedHandler updateCompleted;
        public event UpdateProgressHandler progressUpdated;
        public event UpdateStatusHandler statusUpdated;
        public event UpdatePausedHandler updatePaused;

        const int RETURN_THREAD_EXIT = 1;
        const int RETURN_ERROR = 2;
        const int RETURN_NORMAL = 3;

        ManualResetEvent manualResetEventPause = new ManualResetEvent(true);
        ManualResetEvent manualResetEventStop = new ManualResetEvent(true);

        int uroflowProfilID = -2;

        readonly int UROFLOW_DEVICE_ENUM = 2;

        bool onlyOne = false;

        int patientCount = 0;

        Stopwatch watch = null;
        double oldMilis = 0;
        TimeEstimation timeEstimation = null;
        String DBLocumFilePath;
        String DBItriFilePath;
        
        
        SqlConnection sqlConLocum;
        SqlConnection sqlConItri;

        public LocumUpdate(String DBLocumFilePath,String DBItriFilePath) {
            this.DBItriFilePath = DBItriFilePath;
            this.DBLocumFilePath = DBLocumFilePath;
        }

        public void start() {

            Thread th = new Thread(delegate()
            {
                startUpdateDB();
            });
            th.IsBackground = true;
            th.Start();
        }

        public void startClean() { 
        
            Thread th = new Thread(delegate()
            {
                if (cleanAll())
                {
                    updateCompleted("");
                }
                else updateCompleted("hata");
            });
            th.IsBackground = true;
            th.Start();            
        }

        private void startUpdateDB() {

            manualResetEventStop.Set();
            manualResetEventPause.Set();
            watch = null;
            oldMilis = 0;

            if (timeEstimation != null)
                timeEstimation.reset();
            
            statusFunction("Güncelleme Başladı");
            try
            {
                String sqlConStringLocum = "Data Source=.\\SQLEXPRESS;AttachDbFilename="
                     + DBLocumFilePath + ";Integrated Security=True;Connect Timeout=6;User Instance=True";

                String sqlConStringItri = "Data Source=.\\SQLEXPRESS;AttachDbFilename="
                     + DBItriFilePath + ";Integrated Security=True;Connect Timeout=6;User Instance=True";


                bool canConnnectLocum = DBFunctions.hasConnection(sqlConStringLocum, ref sqlConLocum);
                if (!canConnnectLocum)
                {
                    sqlConStringLocum = sqlConStringLocum.Replace("User Instance=True", "User Instance=False");
                    canConnnectLocum = DBFunctions.hasConnection(sqlConStringLocum, ref sqlConLocum);
                }

                bool canConnectItri = DBFunctions.hasConnection(sqlConStringItri, ref sqlConItri);
                if (!canConnectItri)
                {
                    sqlConStringItri = sqlConStringItri.Replace("User Instance=True", "User Instance=False");
                    canConnectItri = DBFunctions.hasConnection(sqlConStringItri, ref sqlConItri);
                }

                if (!canConnnectLocum || !canConnectItri)
                {
                    updateCompletedFunction("Database'lerden birisini açamadı-Log dosyalarını temizleyin.");
                    return; 
                }                
            }
            catch (Exception)
            {
                /*sqlConLocum = null;
                sqlConItri = null;
                Logs.logList.Add(ex.Message);
                if (ex.Message.Contains("One or more files do not match the primary file of the database"))
                {
                    if(updateCompleted!=null){
                       updateCompleted("Log Dosyalarını temizleyin");
                    }                    
                }
                else if (ex.Message.Contains("asd")) {}*/                             
            }            
            statusFunction("Güncelleme Başladı.");

            uroflowProfilID = getUroFlowProfilID(sqlConLocum);
            if (uroflowProfilID < 0)
            {
                updateCompletedFunction("Uroflow Profili Bulunamadı");
                return;
            }
            statusFunction("Güncelleme Başladı..");

            bool LocumCheck = errorCheckLocum();
            if (!LocumCheck) { 
                updateCompletedFunction("Seçili Locum Database'i sorunlu");
                return;
            }

            statusFunction("Güncelleme Başladı...");

            bool ItriCheck = errorCheckItri();
            if (!ItriCheck) {
                updateCompletedFunction("Seçili Itri Database'i sorunlu");
                return;
            }

            bool res=controlPauseAndStop();
            if (!res) {
                updateCompletedFunction("");
                return;          
            }

            statusFunction("Güncelleme Başladı....");

            patientCount = DBFunctions.getTableRowCount(sqlConItri, "patients");
            
            statusFunction("Hastaneler Güncelleniyor");

            oldMilis = 0;
            watch = new Stopwatch();
            timeEstimation = new TimeEstimation(patientCount);

            int resInt=updateHospitals();
            if (resInt == RETURN_THREAD_EXIT) {
                updateCompletedFunction("");
                return;                
            }

            statusFunction("Doktorlar Güncelleniyor");
            resInt=updateDoctors();
            if (resInt == RETURN_THREAD_EXIT)
            {
                updateCompletedFunction("");
                return;
            }

            statusFunction("Departmanlar Güncelleniyor");
            resInt=updateDeparments();
            if (resInt == RETURN_THREAD_EXIT)
            {
                updateCompletedFunction("");
                return;
            }

            statusFunction("Hastalar Güncelleniyor");
            resInt=updatePatients();
            if (resInt == RETURN_THREAD_EXIT)
            {
                updateCompletedFunction("");
                return;
            }

            statusFunction("Güncelleme Tamamlandı.");
            try
            {
                if (sqlConItri.State == ConnectionState.Open)
                    sqlConItri.Close();

                if (sqlConLocum.State == ConnectionState.Open)
                    sqlConLocum.Close();
            }
            catch (Exception) { }

            // Thread Sonlandı.
            updateCompletedFunction("");
        }
       
        private void statusFunction(String result) {

            if (statusUpdated != null) {
                statusUpdated(result);
            }
        }
       
        private void updateCompletedFunction(String result)
        {
            if (updateCompleted != null)
            {
                updateCompleted(result);
            }
        }
                   
        private bool controlPauseAndStop()
        {
            // Can be paused here
            if (manualResetEventPause.WaitOne(0)) { }
            else
            {
                // Thread Paused we can fire an event.
                if (updatePaused != null)
                {
                    updatePaused();
                }
                manualResetEventPause.WaitOne();
            }

            //Can be stopped here
            if (!manualResetEventStop.WaitOne(0))
            {
                return false;                
            }
            return true;
        }
       
        /// <summary>
        /// Pause or Continue
        /// </summary>
        /// <param name="pause"></param>
        public void pauseOperations(bool pause)
        {
            if (pause)
                manualResetEventPause.Reset();
            else
            {
                manualResetEventPause.Set();
            }
        }

        public void stopOperations(bool stop)
        {
            pauseOperations(!stop);//If in pause state destroy and continue to stop.
            if (stop)
                manualResetEventStop.Reset();
            else
            {
                manualResetEventStop.Set();
            }
        }

        private int getUroFlowProfilID(SqlConnection sqlCon)
        {
            try
            {
                DataSet dsTests = DBFunctions.fillDataset("select id,Deviceid from tests where Name like 'uroflow'", sqlCon);
                if (dsTests.Tables[0].Rows.Count > 0)
                {

                    foreach (DataRow dr in dsTests.Tables[0].Rows)
                    {
                        int devid = Convert.ToInt32(dr["deviceid"].ToString());
                        if (devid == UROFLOW_DEVICE_ENUM)
                        {
                            int id = Convert.ToInt32(dr["id"].ToString());
                            return id;
                        }
                    }
                }
                return -1;
            }
            catch
            {
                return -1;
            }
        }

        private bool errorCheckLocum()
        {
            try
            {
                DataSet ds = DBFunctions.fillDataset("select * from LabelTypes where id=-1", sqlConLocum);
                return true;
            }
            catch
            {
                return false;
            }
        }

        private bool errorCheckItri()
        {
            try
            {
                DataSet ds = DBFunctions.fillDataset("select * from UroFlowValues", sqlConItri);
                return true;
            }
            catch(Exception ex)
            {
                Logs.logList.Add(" errorCheckItri() "+ ex.Message);
                return false;
            }
        }

        private int updateHospitals()
        {
            try
            {
                SortedList<String, Type> locumHospitalKeyList =
                    DBFunctions.getKeyList(sqlConLocum, "select * from Hospital where id=-1", null);

                DataSet dsItri = DBFunctions.fillDataset("select * from Hospital", sqlConItri);
                if (dsItri.Tables[0].Rows.Count > 0)
                {

                    bool ret = controlPauseAndStop();
                    if (!ret) return RETURN_THREAD_EXIT;

                    String sqlCmd = "INSERT INTO Hospital (";
                    sqlCmd = DBFunctions.prepareInsertCommand(sqlCmd, locumHospitalKeyList);
                    SqlCommand commandHospitalLocum = new SqlCommand(sqlCmd, sqlConLocum);

                    foreach (DataRow dr in dsItri.Tables[0].Rows)
                    {   // Traverse all rows in a table 
                        DBFunctions.initializeSqlParameters(commandHospitalLocum, locumHospitalKeyList);
                        DBFunctions.mergeDataRowWithParameters(commandHospitalLocum, dr, "id");

                        if (sqlConLocum.State == ConnectionState.Closed)
                            sqlConLocum.Open();

                        commandHospitalLocum.ExecuteNonQuery();
                        sqlConLocum.Close();
                    }
                }
                return RETURN_NORMAL;
            }
            catch (Exception ex) {
                Logs.logList.Add(ex.Message);
                return RETURN_ERROR; 
            }
        }

        private bool updatePatientDatas(int locumPatientTestID, int itriPatientTestId, double testTime)
        {
            try
            {
                SortedList<String, Type> locumPatientDataKeyList =
                    DBFunctions.getKeyList(sqlConLocum, "select * from patientdata where id=-1", null);

                DataSet dsItri = DBFunctions.fillDataset("select * from patientdata where Patienttestid=" + itriPatientTestId, sqlConItri);
                if (dsItri.Tables[0].Rows.Count > 0)
                {
                    String sqlCmd = "INSERT INTO Patientdata (";
                    sqlCmd = DBFunctions.prepareInsertCommand(sqlCmd, locumPatientDataKeyList);
                    SqlCommand commandInsertPatientDataLocum = new SqlCommand(sqlCmd, sqlConLocum);

                    double timeCurrent = 0;
                    double gap = testTime / (double)dsItri.Tables[0].Rows.Count;
                    int currentRow = 0;
                    foreach (DataRow dr in dsItri.Tables[0].Rows)
                    {  // Traverse all rows in a table 
                        DBFunctions.initializeSqlParameters(commandInsertPatientDataLocum, locumPatientDataKeyList);
                        commandInsertPatientDataLocum.Parameters["@Patienttestid"].Value = locumPatientTestID;
                        commandInsertPatientDataLocum.Parameters["@ResultTime"].Value = timeCurrent;

                        int ItriPatientTestId = DBFunctions.mergeDataRowWithParameters(commandInsertPatientDataLocum, dr, "id");

                        updateIMarker(dr, locumPatientTestID, currentRow);

                        if (sqlConLocum.State == ConnectionState.Closed)
                            sqlConLocum.Open();


                        commandInsertPatientDataLocum.ExecuteNonQuery();
                        sqlConLocum.Close();

                        timeCurrent += gap;
                        currentRow++;
                    }
                }
                return true;
            }
            catch (Exception ex) {
                Logs.logList.Add(ex.Message);
                return false; 
            }
        }

        private bool updateIMarker(DataRow dr, int locumPatientTestID, int startIndex)
        {
            try
            {
                object valMarkerType = dr["MarkerType"];
                if (valMarkerType != DBNull.Value)
                {
                    object valMarkerEndIndex = dr["MarkerEndIndex"];
                    object valMarkerText = dr["MarkerText"];
                    int mType = -1;

                    if (valMarkerEndIndex == DBNull.Value)
                    {
                        valMarkerEndIndex = -1;
                        if (valMarkerType.ToString().Equals("1")) // Max Flow
                        {
                            mType = 50;
                        }
                        else { }
                    }
                    else
                    {
                        if (valMarkerType.ToString().Equals("1")) // Boşaltma
                        {
                            mType = 3;
                        }
                        else if (valMarkerType.ToString().Equals("5")) // Artifact
                        {
                            mType = 4;
                        }
                        else
                        { }
                    }
                    if (mType == -1) return false;

                    // Locum patient data select           
                    SortedList<String, Type> locumLabelsKeyList =
                        DBFunctions.getKeyList(sqlConLocum, "select * from Labels where id=-1", null);
                    // Insert Locum Patient Tests Part
                    String sqlCmd = "INSERT INTO Labels (";
                    sqlCmd = DBFunctions.prepareInsertCommand(sqlCmd, locumLabelsKeyList);
                    SqlCommand commandInsertPatientDataLocum = new SqlCommand(sqlCmd, sqlConLocum);

                    DBFunctions.initializeSqlParameters(commandInsertPatientDataLocum, locumLabelsKeyList);

                    commandInsertPatientDataLocum.Parameters["@TestId"].Value = locumPatientTestID;
                    commandInsertPatientDataLocum.Parameters["@StartIndex"].Value = startIndex;
                    commandInsertPatientDataLocum.Parameters["@EndIndex"].Value = valMarkerEndIndex;
                    commandInsertPatientDataLocum.Parameters["@MarkerType"].Value = mType;

                    if (sqlConLocum.State == ConnectionState.Closed)
                        sqlConLocum.Open();

                    commandInsertPatientDataLocum.ExecuteNonQuery();
                    sqlConLocum.Close();
                    return true;
                }
                return false;
            }
            catch (Exception ex) {
                Logs.logList.Add(ex.Message);
                return false; 
            }
        }

        private int updateDeparments()
        {
            try
            {
                SortedList<String, Type> locumDeparmentKeyList =
                    DBFunctions.getKeyList(sqlConLocum, "select * from Departments where id=-1", null);

                DataSet dsItri = DBFunctions.fillDataset("select * from Departments", sqlConItri);
                if (dsItri.Tables[0].Rows.Count > 0)
                {
                    String sqlCmd = "INSERT INTO Departments (";
                    sqlCmd = DBFunctions.prepareInsertCommand(sqlCmd, locumDeparmentKeyList);
                    SqlCommand commandDepartmentLocum = new SqlCommand(sqlCmd, sqlConLocum);

                    foreach (DataRow dr in dsItri.Tables[0].Rows)
                    {   // Traverse all rows in a table 


                        bool ret = controlPauseAndStop();
                        if (!ret) return RETURN_THREAD_EXIT;

                        DBFunctions.initializeSqlParameters(commandDepartmentLocum, locumDeparmentKeyList);
                        DBFunctions.mergeDataRowWithParameters(commandDepartmentLocum, dr, "id");

                        if (sqlConLocum.State == ConnectionState.Closed)
                            sqlConLocum.Open();

                        commandDepartmentLocum.ExecuteNonQuery(); // ExecuteNonQuery();
                        sqlConLocum.Close();
                    }
                }
                return RETURN_NORMAL;
            }
            catch (Exception ex) {
                Logs.logList.Add(ex.Message);
                return RETURN_ERROR; 
            }
        }

        private int updateDoctors()
        {
            try
            {
                ArrayList excludeList = new ArrayList();
                excludeList.Add("namesurname");

                // Locum patient data select           
                SortedList<String, Type> locumDoctorKeyList =
                    DBFunctions.getKeyList(sqlConLocum, "select * from Doctors where id=-1", excludeList);

                // Itri patient data select
                DataSet dsItri = DBFunctions.fillDataset("select * from Doctors", sqlConItri);
                if (dsItri.Tables[0].Rows.Count > 0)
                {
                    String sqlCmd = "INSERT INTO Doctors (";
                    sqlCmd = DBFunctions.prepareInsertCommand(sqlCmd, locumDoctorKeyList);
                    SqlCommand commandDoctorLocum = new SqlCommand(sqlCmd, sqlConLocum);

                    foreach (DataRow dr in dsItri.Tables[0].Rows)
                    {
                        bool ret = controlPauseAndStop();
                        if (!ret) return RETURN_THREAD_EXIT;

                        // Traverse all rows in a table 
                        DBFunctions.initializeSqlParameters(commandDoctorLocum, locumDoctorKeyList);
                        DBFunctions.mergeDataRowWithParameters(commandDoctorLocum, dr, "id");

                        if (sqlConLocum.State == ConnectionState.Closed)
                            sqlConLocum.Open();

                        commandDoctorLocum.ExecuteNonQuery(); // ExecuteNonQuery();
                        sqlConLocum.Close();
                    }
                }
                return RETURN_NORMAL;
            }
            catch (Exception ex) {
                Logs.logList.Add(ex.Message);
                return RETURN_ERROR; 
            }
        }

        private int updatePatients()
        {
            try
            {
                int currentPatient = 0;

                SortedList<String, Type> locumPatientsKeyList = DBFunctions.getKeyList(sqlConLocum, "select * from Patients where id=-1", null);
                DataSet dsItri = DBFunctions.fillDataset("select * from Patients", sqlConItri);

                String sqlCmd = "INSERT INTO Patients (";
                sqlCmd = DBFunctions.prepareInsertCommand(sqlCmd, locumPatientsKeyList);
                sqlCmd += "select @@identity as Int";

                if (dsItri.Tables[0].Rows.Count > 0)
                {
                    SqlCommand commandInsertPatientLocum = new SqlCommand(sqlCmd, sqlConLocum);
                    if (!watch.IsRunning) watch.Start();
                    foreach (DataRow dr in dsItri.Tables[0].Rows)
                    {
                        bool res = controlPauseAndStop();
                        if (!res)
                        {
                            return RETURN_THREAD_EXIT;
                        }
                        // Traverse all rows in a table                     
                        DBFunctions.initializeSqlParameters(commandInsertPatientLocum, locumPatientsKeyList);
                        int ItriPatientId = DBFunctions.mergeDataRowWithParameters(commandInsertPatientLocum, dr, "id");

                        // get id to update related tables.
                        object ret = DBFunctions.executeMyScalar(commandInsertPatientLocum, sqlConLocum);

                        int LocumPatientId = Convert.ToInt32(ret);
                        int updateTestRes = updatePatientTests(LocumPatientId, ItriPatientId);
                        if (updateTestRes == RETURN_THREAD_EXIT) return RETURN_THREAD_EXIT;

                        if (onlyOne) break;

                        currentPatient++;

                        startProgressBarEvent(currentPatient, patientCount);
                    }
                }
                return RETURN_NORMAL;
            }
            catch (Exception ex) {
                Logs.logList.Add(ex.Message);
                return RETURN_ERROR;
            }
        }

        private int updatePatientTests(int locumPatientID, int itriPatientId)
        {
            try
            {
                SortedList<String, Type> locumPatientTestKeyList = DBFunctions.getKeyList(sqlConLocum, "select * from Patienttests where id=-1", null);
                DataSet dsItri = DBFunctions.fillDataset("select * from Patienttests where Patientid=" + itriPatientId, sqlConItri);

                if (dsItri.Tables[0].Rows.Count > 0)
                {
                    String sqlCmd = "INSERT INTO Patienttests (";
                    sqlCmd = DBFunctions.prepareInsertCommand(sqlCmd, locumPatientTestKeyList);
                    sqlCmd += "select @@identity as Int";
                    SqlCommand commandInsertPatientTestLocum = new SqlCommand(sqlCmd, sqlConLocum);

                    foreach (DataRow dr in dsItri.Tables[0].Rows)
                    {   // Traverse all rows in a table 

                        bool ret = controlPauseAndStop();
                        if (!ret) return RETURN_THREAD_EXIT;

                        commandInsertPatientTestLocum.Parameters.Clear();
                        DBFunctions.initializeSqlParameters(commandInsertPatientTestLocum, locumPatientTestKeyList);
                        commandInsertPatientTestLocum.Parameters["@Patientid"].Value = locumPatientID;
                        commandInsertPatientTestLocum.Parameters["@TestId"].Value = uroflowProfilID;

                        double testTime = WindowUtils.timeStringToDouble(dr["testTime"].ToString());

                        int ItriPatientTestId = DBFunctions.mergeDataRowWithParameters(commandInsertPatientTestLocum, dr, "id");

                        // get id to update related tables.
                        object temp = DBFunctions.executeMyScalar(commandInsertPatientTestLocum, sqlConLocum);

                        int LocumPatientTestId = Convert.ToInt32(temp);
                        updatePatientDatas(LocumPatientTestId, ItriPatientTestId, testTime);
                        if (onlyOne) break;

                    }
                }
                return RETURN_NORMAL;
            }
            catch (Exception ex) {
                Logs.logList.Add(ex.Message);
                return RETURN_ERROR; 
            }
        }

        private bool cleanRecordsLocum()
        {
            SqlCommand cmdDeleteAll = new SqlCommand(
                "delete from Hospital\n" +
                "delete from Doctors\n" +
                "delete from Departments\n" +
                "delete from Labels\n" +
                "delete from Patientdata\n" +
                "delete from Patienttests\n" +
                "delete from Patients\n", sqlConLocum);
            if (sqlConLocum.State == ConnectionState.Closed)
            {
                sqlConLocum.Open();
            }
            cmdDeleteAll.ExecuteNonQuery();
            sqlConLocum.Close();

            return true;
        }

        private bool cleanAll()
        {
            try
            {               
                String sqlConStringLocum = "Data Source=.\\SQLEXPRESS;AttachDbFilename="
                     + DBLocumFilePath + ";Integrated Security=True;Connect Timeout=6;User Instance=True";

                bool canConnnectLocum = DBFunctions.hasConnection(sqlConStringLocum, ref sqlConLocum);
                if (!canConnnectLocum)
                {
                    sqlConStringLocum = sqlConStringLocum.Replace("User Instance=True", "User Instance=False");
                    canConnnectLocum = DBFunctions.hasConnection(sqlConStringLocum, ref sqlConLocum);
                }

                cleanRecordsLocum();                
            }
            catch (Exception ex)
            {
                Logs.logList.Add(ex.Message);                
                return false;                
            }
            return true;
        }

        private void startProgressBarEvent(int currentData, int maxDataCount)
        {            
            double currentTimeMilis = watch.Elapsed.TotalMilliseconds;
            double timeToFinish = timeEstimation.addNewSample(currentTimeMilis - oldMilis, currentData);

            oldMilis = currentTimeMilis;
            if (timeToFinish < 0) return;

            if (progressUpdated != null)
            {
                progressUpdated(currentData, maxDataCount, timeToFinish);
            }
        
        }
    }
}
